﻿using CK.Sprite.Form.Core;
using CK.Sprite.Framework;
using Dapper;
using Dapper.Contrib.Extensions;
using JetBrains.Annotations;
using Newtonsoft.Json.Linq;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CK.Sprite.Form.MySql
{
    public class SpriteCommonRepository : BaseSpriteRepository<SpriteView>, ISpriteCommonRepository
    {
        public SpriteCommonRepository(IUnitOfWork unitOfWork) : base(unitOfWork) { }

        public async Task<List<T>> GetCommonList<T>(string tableName, List<QueryWhereModel> queryWhereModels, string fields = default)
        {
            JObject paramValues = new JObject();
            string strSql = $"SELECT {(string.IsNullOrEmpty(fields) ? "*" : fields)} FROM {tableName} WHERE {CreateSqlWhere(queryWhereModels, paramValues)};";
            var result = await _unitOfWork.Connection.QueryAsync<T>(strSql, paramValues.ToConventionalDotNetObject());
            return result.ToList();
        }

        public async Task<List<T>> GetCommonList2<T>(string tableName, ExpressSqlModel expressSqlModel, string fields = default)
        {
            JObject paramValues = new JObject();
            string strSql = $"SELECT {(string.IsNullOrEmpty(fields) ? "*" : fields)} FROM {tableName} WHERE {CreateSqlWhere(expressSqlModel, paramValues)};";
            var result = await _unitOfWork.Connection.QueryAsync<T>(strSql, paramValues.ToConventionalDotNetObject());
            return result.ToList();
        }

        public async Task DeleteSpriteFormOrView(List<Guid> ids)
        {
            var strSql = @"DELETE FROM SpriteForms WHERE Id IN @Ids;
DELETE FROM SpriteViews WHERE Id IN @Ids;
DELETE FROM FormItems WHERE FormId IN @Ids;
DELETE FROM FormRows WHERE FormId IN @Ids;
DELETE FROM FormCols WHERE FormId IN @Ids;
DELETE FROM ItemViewRows WHERE ViewId IN @Ids;
DELETE FROM ItemViewCols WHERE ViewId IN @Ids;
DELETE FROM ListViewFilters WHERE ViewId IN @Ids;
DELETE FROM ListViewCustomerColumns WHERE ViewId IN @Ids;
DELETE FROM SpriteRules WHERE BusinessId IN @Ids;
DELETE FROM RuleActions WHERE BusinessId IN @Ids;
DELETE FROM WrapInfos WHERE BusinessId IN @Ids;
DELETE FROM Controls WHERE BusinessId IN @Ids;";
            await _unitOfWork.Connection.ExecuteAsync(strSql, new { Ids = ids.ToArray() });
        }

        public async Task<T> GetDataById<T>(string tableName, Guid id)
        {
            JObject paramValues = new JObject();
            string strSql = $"SELECT * FROM {tableName} WHERE Id=@Id;";
            return await _unitOfWork.Connection.QueryFirstOrDefaultAsync<T>(strSql, new { Id = id});
        }

        public async Task<List<T>> GetDatasById<T>(string tableName, Guid id, string fieldName = "")
        {
            JObject paramValues = new JObject();
            string strSql = $"SELECT * FROM {tableName} WHERE {(string.IsNullOrEmpty(fieldName)? "Id":fieldName)} = @Id;";
            var results = await _unitOfWork.Connection.QueryAsync<T>(strSql, new { Id = id });
            return results.ToList();
        }

        public async Task DeleteById(string tableName, Guid id, string fieldName = "")
        {
            JObject paramValues = new JObject();
            string strSql = $"DELETE FROM {tableName} WHERE {(string.IsNullOrEmpty(fieldName) ? "Id" : fieldName)} = @Id;";
            await _unitOfWork.Connection.ExecuteAsync(strSql, new { Id = id });
        }

        public async Task DeleteByIds(string tableName, List<Guid> ids, string fieldName = "")
        {
            JObject paramValues = new JObject();
            string strSql = $"DELETE FROM {tableName} WHERE {(string.IsNullOrEmpty(fieldName) ? "Id" : fieldName)} IN @Ids;";
            await _unitOfWork.Connection.ExecuteAsync(strSql, new { Ids = ids.ToArray() });
        }
    }
}
